﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using HK2009.Common;
using HK2009.BusinessFacade;
namespace HK2009.BusinessFacadeHandler
{
    public class SyAutoSendEmail:ISyAutoSendEmail
    {
        DataBase db = new DataBase();

        public void TimeTaskSendEmail()
        {
            //提取需要发送的distinct email集合

            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@Status", "0");
            string sqlStr = @"select distinct Email from Sy_Recipient as SR inner join Sy_PendingMail as SPM on SR.PendingMailID=SPM.PKID
                              where SR.Status=@Status ";
            DataTable dt = db.RunSqlTab(sqlStr, parms);
            if (dt != null && dt.Rows.Count > 0)
            {
                SqlParameter[] parmsE = new SqlParameter[2];
                DataTable dtC = new DataTable();
                StringBuilder strB = new StringBuilder();

                for (int i = 0; i < dt.Rows.Count; i++)//循环Email集合,把需要发到该email下的邮件内容合起来,并发送.
                {
                    if (StringUtil.IsEmailAddress(dt.Rows[i]["Email"].ToString()))
                    {
                        parmsE[0] = new SqlParameter("@Status", "0");
                        parmsE[1] = new SqlParameter("@Email", dt.Rows[i]["Email"].ToString());
                        sqlStr = @"select SPM.*,SR.Status,SR.Email from Sy_PendingMail as SPM inner join Sy_Recipient as SR on SR.PendingMailID=SPM.PKID
                                    where Status=@Status and Email=@Email ";
                        dtC = db.RunSqlTab(sqlStr, parmsE);

                        if (dtC != null && dtC.Rows.Count > 0)//把发到同一email下的Email内容集合起来
                        {
                            string title = @"Hello, [title]: 


some reminds from special-line,

";
                            title.Replace("[title]", dt.Rows[i]["Email"].ToString());
                            strB.Append(title);
                            for (int j = 0; j < dtC.Rows.Count; j++)
                            {
                                strB.Append(dtC.Rows[j]["App"].ToString());
                                strB.Append(@"\r\n");
                                strB.Append(dtC.Rows[j]["Content"].ToString());
                            }

                            //发送
                            string subject = "reminds from special-line!";

                            SendMail send = new SendMail();
                            string content = strB.ToString();
                            send.MailBody = content;
                            send.MailSubject = subject;
                            send.MailTo = dt.Rows[i]["Email"].ToString();
                            try
                            {
                                send.Send();

                                //发送成功后修改状态
                                SqlParameter[] parmsU = new SqlParameter[3];
                                parmsU[0] = new SqlParameter("@Status", "1");
                                parmsU[1] = new SqlParameter("@Email", dt.Rows[i]["Email"].ToString());
                                parmsU[2] = new SqlParameter("@SendTime", DateTime.Now);
                                sqlStr = "update Sy_Recipient set Status=@Status,SendTime=@SendTime where Email=@Email";
                                db.ExecSqlTransaction(sqlStr, parmsU);
                            }
                            catch
                            {

                            }
                            finally
                            {
                                strB.Remove(0, strB.ToString().Length);
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 添加发送Email的相关内容到数据库
        /// </summary>
        /// <param name="app">应用编号</param>
        /// <param name="content">Email内容</param>
        /// <param name="emailList">要发送的Email集合</param>
        /// <param name="userID">userID</param>
        public void InsertEmailContent(string app, string content, List<string> emailList, int userID)
        {
            List<SqlParameter> lst = new List<SqlParameter>();
            SqlParameter[] parms = null;
            string sqlStr = string.Empty;

            lst.Add(new SqlParameter("@App", app));
            lst.Add(new SqlParameter("@Content", content));
            lst.Add(new SqlParameter("@AddTime", DateTime.Now));

            lst.Add(new SqlParameter("@Creater", userID.ToString()));
            SqlParameter parmR = new SqlParameter("@ReturnValues", 0);
            parmR.Direction = ParameterDirection.Output;
            parmR.SqlDbType = SqlDbType.Int;
            lst.Add(parmR);

            sqlStr += "INSERT INTO Sy_PendingMail(App,AddTime,Creater,Content) VALUES(@App,@AddTime,@Creater,@Content) set @ReturnValues=@@Identity;";

            if (emailList != null && emailList.Count > 0)
            {
                for (int i = 0; i < emailList.Count; i++)
                {
                    lst.Add(new SqlParameter("@Status" + i.ToString(), "0"));
                    lst.Add(new SqlParameter("@SendTime" + i.ToString(), System.DBNull.Value));
                    lst.Add(new SqlParameter("@Email" + i.ToString(), emailList[i]));

                    sqlStr += "INSERT INTO Sy_Recipient(PendingMailID,Email,Status,SendTime) VALUES(@ReturnValues,@Email" + i.ToString() + ",@Status" + i.ToString() + ",@SendTime" + i.ToString() + ");";

                }
            }

            parms = lst.ToArray();
            try
            {
                db.ExecSqlTransaction(sqlStr, parms);
            }
            catch
            {

            }


        }

        /// <summary>
        /// 添加发送Email的相关内容到数据库
        /// </summary>
        /// <param name="app">应用编号</param>
        /// <param name="content">Email内容</param>
        /// <param name="emailList">要发送的Email集合</param>
        /// <param name="userID">userID</param>
        public void InsertEmailContent(string app, string content, string[] emailList, int userID)
        {
            List<SqlParameter> lst = new List<SqlParameter>();
            SqlParameter[] parms = null;
            string sqlStr = string.Empty;

            lst.Add(new SqlParameter("@App", app));
            lst.Add(new SqlParameter("@Content", content));
            lst.Add(new SqlParameter("@AddTime", DateTime.Now));

            lst.Add(new SqlParameter("@Creater", userID.ToString()));
            SqlParameter parmR = new SqlParameter("@ReturnValues", 0);
            parmR.Direction = ParameterDirection.Output;
            parmR.SqlDbType = SqlDbType.Int;
            lst.Add(parmR);

            sqlStr += "INSERT INTO Sy_PendingMail(App,AddTime,Creater,Content) VALUES(@App,@AddTime,@Creater,@Content) set @ReturnValues=@@Identity;";

            if (emailList != null && emailList.Length > 0)
            {
                for (int i = 0; i < emailList.Length; i++)
                {
                    lst.Add(new SqlParameter("@Status" + i.ToString(), "0"));
                    lst.Add(new SqlParameter("@SendTime" + i.ToString(), System.DBNull.Value));
                    lst.Add(new SqlParameter("@Email" + i.ToString(), emailList[i]));

                    sqlStr += "INSERT INTO Sy_Recipient(PendingMailID,Email,Status,SendTime) VALUES(@ReturnValues,@Email" + i.ToString() + ",@Status" + i.ToString() + ",@SendTime" + i.ToString() + ");";

                }
            }

            parms = lst.ToArray();
            try
            {
                db.ExecSqlTransaction(sqlStr, parms);
            }
            catch
            {

            }

        }
    }
}
